﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WF_Business;
using WF_DataAccess;
using System.Data;
using System.Web;

namespace Business.FlowOperation
{
    /// <summary>
    /// 业务统计
    /// </summary>
    public class BusinessStatistics
    {
        /// <summary>
        /// 根据wid获取业务逻辑
        /// </summary>
        /// <param name="Request"></param>
        /// <returns></returns>
        public static DataTable GetBusinessByWids(HttpRequest Request)
        {
            DataTable table;
            string sql = "select a.iid,a.wid,a.status from st_instance a";
            if (!string.IsNullOrEmpty(Request["hidYwlx"]) && (Request["hidYwlx"] != "-1"))
            {
                sql += " where a.wid in (" + GetSqlByWid(Request["hidYwlx"]) + ")";
            }
            else
            {
                sql +=  string.Format(" where a.wid in (select wid from st_workflow where  type <>{0} and type<>{1})", 14, 15);
            }
            if (!string.IsNullOrEmpty(Request["txtBtime"]))
            {
                if (DataFactory.DefaultDbType == DatabaseType.Oracle)
                {
                    sql += string.Format(" and  a.accepted_time >=to_date('{0}','yyyy-mm-dd hh24:mi:ss')", Request["txtBtime"] + " 00:00:00");
                }
                else
                {
                    sql += string.Format(" and  a.accepted_time >='{0}'", Request["txtBtime"] + " 00:00:00");
                }
            }
            if (!string.IsNullOrEmpty(Request["txtEtime"]))
            {
                if (DataFactory.DefaultDbType == DatabaseType.Oracle)
                {
                    sql += string.Format(" and  a.accepted_time <=to_date('{0}','yyyy-mm-dd hh24:mi:ss')", Request["txtEtime"] + " 23:59:59");
                }
                else
                {
                    sql += string.Format(" and  a.accepted_time <='{0}'", Request["txtEtime"] + " 23:59:59");
                }
            }
            SysParams.OAConnection().RunSql(sql, out table);
            table.TableName = "business";
            return table;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="Request"></param>
        /// <returns></returns>
        public static DataTable GetBusinessCount(HttpRequest Request)
        {
            DataTable table;
            StringBuilder builder = new StringBuilder();
            builder.Append("select si.iid,si.name,si.wid,sw.wname,si.priority,si.accepted_time,si.exusedtime,si.extotaltime,si.exalarmtime,si.finish_time ");
            builder.Append(" from st_instance si,st_workflow sw where si.wid=sw.wid ");
            if (!string.IsNullOrEmpty(Request["hidYwlx"]))
            {
                if ((Request["hidYwlx"] == "") || (Request["hidYwlx"] == "-1"))
                {
                    builder.AppendFormat("  and sw.type<>{0} and sw.type<>{1}", 14, 15);
                }
                else
                {
                    builder.AppendFormat(" and sw.wid in ({0})", GetSqlByWid(Request["hidYwlx"]));
                }
            }
            if (!string.IsNullOrEmpty(Request["txtBtime"]))
            {
                if (DataFactory.DefaultDbType == DatabaseType.Oracle)
                {
                    builder.AppendFormat(" and accepted_time >= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", Request["txtBtime"] + " 00:00:00");
                }
                else if (DataFactory.DefaultDbType == DatabaseType.SqlServer)
                {
                    builder.AppendFormat(" and accepted_time >='{0}'", Request["txtBtime"] + " 00:00:00");
                }
            }
            if (!string.IsNullOrEmpty(Request["txtEtime"]))
            {
                if (DataFactory.DefaultDbType == DatabaseType.Oracle)
                {
                    builder.AppendFormat(" and accepted_time <= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", Request["txtEtime"] + " 23:59:59");
                }
                else if (DataFactory.DefaultDbType == DatabaseType.SqlServer)
                {
                    builder.AppendFormat(" and accepted_time <= '{0}'", Request["txtEtime"] + " 23:59:59");
                }
            }
            SysParams.OAConnection().RunSql(builder.ToString(), out table);
            return table;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="wids"></param>
        /// <returns></returns>
        private static string GetSqlByWid(string wids)
        {
            return string.Format(" select a.wid from st_workflow a,st_workflow b where  a.wname=b.wname and  b.wid in ({0}) ", wids.Trim());
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="Request"></param>
        /// <returns></returns>
        public static DataTable GetStepInfo(HttpRequest Request)
        {
            DataTable table;
            IDataAccess access = SysParams.OAConnection();
            string sql = string.Format("select sname,step_type,timeout,exalarmtime,id,ctlid from st_step where wid='{0}' order by step_type", Request["selYwlx"]);
            access.RunSql(sql, out table);
            return table;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="Request"></param>
        /// <returns></returns>
        public static DataTable GetTimeInfo(HttpRequest Request)
        {
            DataTable table;
            StringBuilder builder = new StringBuilder();
            builder.Append("select iid,userid,step,exusedtime,extotaltime,exendtime,exalarmtime,active,stepctlid ");
            builder.AppendFormat("from st_work_item where iid in\t(select si.iid from st_instance si  where si.wid='{0}' ", Request["selYwlx"]);
            if (!string.IsNullOrEmpty(Request["txtBtime"]))
            {
                if (DataFactory.DefaultDbType == DatabaseType.Oracle)
                {
                    builder.AppendFormat(" and si.accepted_time >= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", Request["txtBtime"] + " 00:00:00");
                }
                else if (DataFactory.DefaultDbType == DatabaseType.SqlServer)
                {
                    builder.AppendFormat(" and si.accepted_time >= '{0}'", Request["txtBtime"] + " 00:00:00");
                }
            }
            if (!string.IsNullOrEmpty(Request["txtEtime"]))
            {
                if (DataFactory.DefaultDbType == DatabaseType.Oracle)
                {
                    builder.AppendFormat(" and si.accepted_time <= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", Request["txtEtime"] + " 23:59:59");
                }
                else if (DataFactory.DefaultDbType == DatabaseType.SqlServer)
                {
                    builder.AppendFormat(" and si.accepted_time <= '{0}'", Request["txtEtime"] + " 23:59:59");
                }
            }
            builder.Append(" )");
            builder.Append(" union all ");
            builder.Append("select iid,userid,step,exusedtime,extotaltime,exendtime,exalarmtime,active,stepctlid ");
            builder.AppendFormat("from st_work_item where iid in\t(select si.iid from st_instance si  where si.wid='{0}' ", Request["selYwlx"]);
            if (!string.IsNullOrEmpty(Request["txtBtime"]))
            {
                if (DataFactory.DefaultDbType == DatabaseType.Oracle)
                {
                    builder.AppendFormat(" and si.accepted_time >= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", Request["txtBtime"] + " 00:00:00");
                }
                else if (DataFactory.DefaultDbType == DatabaseType.SqlServer)
                {
                    builder.AppendFormat(" and si.accepted_time >= '{0}'", Request["txtBtime"] + " 00:00:00");
                }
            }
            if (!string.IsNullOrEmpty(Request["txtEtime"]))
            {
                if (DataFactory.DefaultDbType == DatabaseType.Oracle)
                {
                    builder.AppendFormat(" and si.accepted_time <= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", Request["txtEtime"] + " 23:59:59");
                }
                else if (DataFactory.DefaultDbType == DatabaseType.SqlServer)
                {
                    builder.AppendFormat(" and si.accepted_time <= '{0}'", Request["txtEtime"] + " 23:59:59");
                }
            }
            builder.Append(" )");
            SysParams.OAConnection().RunSql(builder.ToString(), out table);
            return table;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="wids"></param>
        /// <returns></returns>
        public static DataTable GetUsersByWids(string wids)
        {
            DataTable table;
            string sql = "select distinct st.userid,st.user_name username from st_user st, st_user_group sug,st_group_in_step sgis where sug.gid=sgis.gid and st.userid=sug.userid";
            if (!string.IsNullOrEmpty(wids) && (wids != "-1"))
            {
                sql = sql + " and sgis.wid in (" + GetSqlByWid(wids) + ")";
            }
            else
            {
                sql = string.Format(sql + " and sgis.wid in (select wid from st_workflow where  type <>{0} and type<>{1})", 14, 15);
            }
            SysParams.OAConnection().RunSql(sql, out table);
            table.TableName = "users";
            return table;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="wids"></param>
        /// <returns></returns>
        public static DataTable GetUsersDepByWids(string wids)
        {
            DataTable table;
            string sql = "select distinct a.userid,a.order_id from st_user_department a,st_group_in_step b,st_user_group c where a.userid=c.userid and b.gid=c.gid";
            if (!string.IsNullOrEmpty(wids) && (wids != "-1"))
            {
                sql = sql + "  and b.wid in(" + GetSqlByWid(wids) + ")";
            }
            else
            {
                sql = string.Format(sql + " and b.wid in (select wid from st_workflow where  type <>{0} and type<>{1})", 14, 15);
            }
            SysParams.OAConnection().RunSql(sql, out table);
            return table;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="Request"></param>
        /// <returns></returns>
        public static DataTable GetWname(HttpRequest Request)
        {
            DataTable table;
            StringBuilder builder = new StringBuilder();
            builder.Append("select wid,wname from st_workflow sw where sw.rot=0 ");
            if (!string.IsNullOrEmpty(Request["hidYwlx"]) && (Request["hidYwlx"] != "-1"))
            {
                builder.AppendFormat(" and sw.wid in ({0}) ", Request["hidYwlx"]);
            }
            else
            {
                builder.AppendFormat(" and sw.type<>{0} and sw.type<>{1}", 14, 15);
            }
            SysParams.OAConnection().RunSql(builder.ToString(), out table);
            return table;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="Request"></param>
        /// <returns></returns>
        public static DataTable GetWorkitemByWids(HttpRequest Request)
        {
            DataTable table;
            string sql = "select a.iid,a.wiid,a.userid,a.step,a.active from st_work_item a,st_instance b,st_workflow c where  a.iid = b.iid and b.wid=c.wid";
            if (!string.IsNullOrEmpty(Request["hidYwlx"]) && (Request["hidYwlx"] != "-1"))
            {
                sql = sql + " and c.wid in (" + GetSqlByWid(Request["hidYwlx"]) + ")";
            }
            else
            {
                sql = string.Format(sql + " and c.wid in (select wid from st_workflow where type <>{0} and type<>{1})", 14, 15);
            }
            if (!string.IsNullOrEmpty(Request["txtBtime"]))
            {
                if (DataFactory.DefaultDbType == DatabaseType.Oracle)
                {
                    sql = sql + string.Format(" and  b.accepted_time >=to_date('{0}','yyyy-mm-dd hh24:mi:ss')", Request["txtBtime"] + " 00:00:00");
                }
                else
                {
                    sql = sql + string.Format(" and  b.accepted_time >='{0}'", Request["txtBtime"] + " 00:00:00");
                }
            }
            if (!string.IsNullOrEmpty(Request["txtEtime"]))
            {
                if (DataFactory.DefaultDbType == DatabaseType.Oracle)
                {
                    sql = sql + string.Format(" and  b.accepted_time <=to_date('{0}','yyyy-mm-dd hh24:mi:ss')", Request["txtEtime"] + " 23:59:59");
                }
                else
                {
                    sql = sql + string.Format(" and  b.accepted_time <='{0}'", Request["txtEtime"] + " 23:59:59");
                }
            }
            SysParams.OAConnection().RunSql(sql, out table);
            table.TableName = "workitem";
            return table;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="Request"></param>
        /// <returns></returns>
        public static DataTable GetWorkitemHistByWids(HttpRequest Request)
        {
            DataTable table;
            string sql = "select a.iid,a.wiid,a.prev_wiid,a.userid,a.step,a.exusedtime,a.extotaltime,a.exendtime,a.exalarmtime from st_work_item_hist a,st_instance b,st_workflow c where  a.iid = b.iid and b.wid=c.wid";
            if (!string.IsNullOrEmpty(Request["hidYwlx"]) && (Request["hidYwlx"] != "-1"))
            {
                sql = sql + " and c.wid in (" + GetSqlByWid(Request["hidYwlx"]) + ")";
            }
            else
            {
                sql = string.Format(sql + " and c.wid in (select wid from st_workflow where  type <>{0} and type<>{1})", 14, 15);
            }
            if (!string.IsNullOrEmpty(Request["txtBtime"]))
            {
                if (DataFactory.DefaultDbType == DatabaseType.Oracle)
                {
                    sql = sql + string.Format(" and  b.accepted_time >=to_date('{0}','yyyy-mm-dd hh24:mi:ss')", Request["txtBtime"] + " 00:00:00");
                }
                else
                {
                    sql = sql + string.Format(" and  b.accepted_time >='{0}'", Request["txtBtime"] + " 00:00:00");
                }
            }
            if (!string.IsNullOrEmpty(Request["txtEtime"]))
            {
                if (DataFactory.DefaultDbType == DatabaseType.Oracle)
                {
                    sql = sql + string.Format(" and  b.accepted_time <=to_date('{0}','yyyy-mm-dd hh24:mi:ss')", Request["txtEtime"] + " 23:59:59");
                }
                else
                {
                    sql = sql + string.Format(" and  b.accepted_time <='{0}'", Request["txtEtime"] + " 23:59:59");
                }
            }
            SysParams.OAConnection().RunSql(sql, out table);
            table.TableName = "workitemhist";
            return table;
        }

    }
}
